package www.worq.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;

import www.worq.controller.beans.reciboBean;

public class recibosDao extends oreqDao {

	public ArrayList<reciboBean> getRECIBOS() throws SQLException {
		ArrayList<reciboBean> inmuebles = new ArrayList<reciboBean>();
		try {
			StringBuilder query = new StringBuilder();
			query.append("Select * from recibo order by status");
			conn = this.getConnectionMYSQL();
			ps = conn.prepareStatement(query.toString());
			rs = ps.executeQuery();
			System.out.println("dao.recibos.getRECIBOS.Ejecutado----------> "
					+ query.toString());

			while (rs.next()) {
				reciboBean recibo = new reciboBean();
				recibo.setId_recibo(rs.getInt(1));
				recibo.setAtendio(rs.getString(2));
				recibo.setNom_tramite(rs.getString(3));
				recibo.setMatricula(rs.getString(4));
				recibo.setCve_catastral(rs.getString(5));
				recibo.setFecha(rs.getString(6));
				recibo.setTipo_pago(rs.getString(7));
				recibo.setTotal(rs.getDouble(8));
				recibo.setStatus(rs.getString(9));
				recibo.setOficina(rs.getString(10));
				inmuebles.add(recibo);
			}
			System.out.println("dao.recibos.getRECIBOS----------> Cargado");
		} catch (Exception e) {
			System.out.println("recibosDao...Error..." + e.getMessage());
		} finally {
			close(conn);
			close(ps);
			close(rs);
		}
		return inmuebles;
	}
	
	public void inmprimeNota(reciboBean recibo) throws SQLException {
		try {
			StringBuilder query = new StringBuilder();
			query.append("Select * from recibo where id_recibo='").append(recibo.getId_recibo()).append("'");
			conn = this.getConnectionMYSQL();
			ps = conn.prepareStatement(query.toString());
			rs = ps.executeQuery();
			System.out.println("dao.recibos.getRECIBOS.Ejecutado----------> "
					+ query.toString());

			while (rs.next()) {
				recibo.setId_recibo(rs.getInt(1));
				recibo.setAtendio(rs.getString(2));
				recibo.setNom_tramite(rs.getString(3));
				recibo.setMatricula(rs.getString(4));
				recibo.setCve_catastral(rs.getString(5));
				recibo.setFecha(rs.getString(6));
				recibo.setTipo_pago(rs.getString(7));
				recibo.setTotal(rs.getDouble(8));
				recibo.setStatus(rs.getString(9));
				recibo.setOficina(rs.getString(10));
			}
			System.out.println("dao.recibos.getRECIBOS----------> Cargado");
		} catch (Exception e) {
			System.out.println("recibosDao...Error..." + e.getMessage());
		} finally {
			close(conn);
			close(ps);
			close(rs);
		}
	}

	public int nextId() {
		int id = 0;
		try {
			StringBuilder query = new StringBuilder();
			query.append("Select id_recibo from recibo");
			conn = this.getConnectionMYSQL();
			ps = conn.prepareStatement(query.toString());
			rs = ps.executeQuery();
			System.out.println("dao.recibos.nextId.Ejecutado----------> "
					+ query.toString());

			while (rs.next()) {
				if (id < rs.getInt(1)) {
					id = rs.getInt(1);
				}
			}
			System.out.println("dao.recibos.nextId----------> Cargado");
		} catch (Exception e) {
			System.out.println("recibosDao...Error..." + e.getMessage());
		} finally {
			close(conn);
			close(ps);
			close(rs);
		}
		return id + 1;
	}

	public void insertRECIBO(reciboBean bean) throws Exception {
		try {
			StringBuilder query = new StringBuilder();
			conn = this.getConnectionMYSQL();

			query.append("INSERT INTO recibo ")
					.append("(id_recibo, atendio, nom_tramite, matricula, cve_catastral, fecha, tipo_pago, total, status,oficina) ")
					.append("values ('").append(bean.getId_recibo())
					.append("','").append(bean.getAtendio()).append("','")
					.append(bean.getNom_tramite()).append("','")
					.append(bean.getMatricula()).append("','")
					.append(bean.getCve_catastral()).append("','")
					.append(bean.getFecha()).append("','")
					.append(bean.getTipo_pago()).append("','")
					.append(bean.getTotal()).append("','")
					.append(bean.getStatus()).append("','")
					.append(bean.getOficina()).append("');");

			System.out.println("dao.recibos.insertRECIBO.Ejecutado----------> "
					+ query.toString());

			st = conn.createStatement();
			st.executeUpdate(query.toString());

			System.out.println("dao.recibos.insertRECIBO----------> insertado");

		} catch (SQLException error) {
			throw new Exception(error.getMessage());
		} catch (Exception error) {
			throw new Exception(error.getMessage());
		} finally {
			close(conn);
			close(st);
		}
	}

	public void deleteRECIBO(reciboBean bean) throws Exception {
		try {
			StringBuilder query = new StringBuilder();
			conn = this.getConnectionMYSQL();

			query.append("DELETE FROM recibo where id_recibo=").append(
					bean.getId_recibo());

			st = conn.createStatement();
			st.executeUpdate(query.toString());
			System.out.println("dao.recibos.deleteRECIBO.Ejecutado----------> "
					+ query.toString());
			System.out.println("dao.recibos.deleteRECIBO----------> eliminado");

		} catch (SQLException error) {
			throw new Exception(error.getMessage());
		} catch (Exception error) {
			throw new Exception(error.getMessage());
		} finally {
			close(conn);
			close(st);
		}
	}

	public void updateRECIBO(reciboBean bean) throws Exception {
		try {
			StringBuilder query = new StringBuilder();
			conn = this.getConnectionMYSQL();

			query.append("UPDATE recibo SET ").append("cve_catastral='")
					.append(bean.getCve_catastral()).append("',tipo_pago='")
					.append(bean.getTipo_pago()).append("' where id_recibo=")
					.append(bean.getId_recibo()).append(";");

			st = conn.createStatement();
			st.executeUpdate(query.toString());
			System.out.println("dao.recibos.updateRECIBO.Ejecutado----------> "
					+ query.toString());
			System.out
					.println("dao.recibos.updateRECIBO----------> actualizado");

		} catch (SQLException error) {
			throw new Exception(error.getMessage());
		} catch (Exception error) {
			throw new Exception(error.getMessage());
		} finally {
			close(conn);
			close(st);
		}
	}

	public void updateSTATUS(reciboBean bean) throws Exception {
		try {
			StringBuilder query = new StringBuilder();
			conn = this.getConnectionMYSQL();

			query.append("UPDATE recibo SET ").append("status='")
					.append(bean.getStatus()).append("' where id_recibo=")
					.append(bean.getId_recibo()).append(";");

			st = conn.createStatement();
			st.executeUpdate(query.toString());
			System.out.println("dao.recibos.updateSTATUS.Ejecutado----------> "
					+ query.toString());
			System.out
					.println("dao.recibos.updateSTATUS----------> actualizado");

		} catch (SQLException error) {
			throw new Exception(error.getMessage());
		} catch (Exception error) {
			throw new Exception(error.getMessage());
		} finally {
			close(conn);
			close(st);
		}
	}
}
